/*
 * 
 */

package com.spartancoder.accommodation.models;

import com.spartancoder.accommodation.beans.UsageBean;
import com.spartancoder.accommodation.db.Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

/**
 *
 * @author andrei
 */
public class Reports {

	private static Logger log = Logger.getLogger(Reports.class.getName());
	private static Database db = Database.getInstance();

	public static List<UsageBean> usageStats() {
		try {
			Connection conn = db.getConnection();
			String query = "select p.place_id, " +
					"( select count(o.option_id) " +
						"from `options` o " +
						"where o.place_id = p.place_id and " +
						"o.session_id = ( " +
							"select `session_id` " +
							"from `accommodation_sessions` " +
							"order by `start_date` desc " +
							"limit 1) ) as `used`, " +
					"(p.rooms * p.places_per_room) as `available` " +
					"from `places` p " +
					"order by `used` desc";
			PreparedStatement st = conn.prepareStatement(query);
			
			ResultSet rs = st.executeQuery();
			List<UsageBean> ret = new LinkedList<UsageBean>();
			while (rs.next()) {
				UsageBean ub = new UsageBean();
				ub.fill(rs);
				ret.add(ub);
			}

			return ret;
		} catch( SQLException ex ) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

}
